package com.mavenq.fly.utils.excelv2;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 完整的 Excel 工具类（生产环境可用）
 * 支持导入导出，列互换，样式自定义等高级功能
 */
public class ExcelUtil {
    
    private static final String DEFAULT_DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
    private static final int DEFAULT_COLUMN_WIDTH = 15;
    private static final int MIN_COLUMN_WIDTH = 8;
    private static final int MAX_COLUMN_WIDTH = 50;
    private static final short DEFAULT_ROW_HEIGHT = 400;
    private static final short HEADER_ROW_HEIGHT = 500;

    // ==================== 导出相关方法 ====================
    
    /**
     * 基础导出
     */
    public static <T> void exportToExcel(List<T> dataList, String[] headers, String[] fieldNames,
                                        String sheetName, OutputStream outputStream, boolean isXlsx) {
        Workbook workbook = null;
        try {
            workbook = isXlsx ? new XSSFWorkbook() : new HSSFWorkbook();
            Sheet sheet = workbook.createSheet(sheetName != null ? sheetName : "Sheet1");
            
            // 设置默认列宽
            setDefaultColumnWidth(sheet, headers.length);
            
            // 创建样式
            CellStyle headerStyle = createHeaderStyle(workbook);
            CellStyle dataStyle = createDataStyle(workbook);
            
            // 创建表头行
            Row headerRow = sheet.createRow(0);
            headerRow.setHeight(HEADER_ROW_HEIGHT);
            
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(headerStyle);
            }
            
            // 填充数据
            if (dataList != null && !dataList.isEmpty()) {
                int rowNum = 1;
                for (T data : dataList) {
                    Row row = sheet.createRow(rowNum++);
                    row.setHeight(DEFAULT_ROW_HEIGHT);
                    fillRowData(row, data, fieldNames, dataStyle);
                }
            }
            
            // 自适应列宽
            autoSizeColumns(sheet, headers.length, dataList, fieldNames);
            
            workbook.write(outputStream);
            
        } catch (Exception e) {
            throw new RuntimeException("导出Excel失败", e);
        } finally {
            closeWorkbook(workbook);
        }
    }
    
    /**
     * 高级导出
     */
    public static <T> void exportToExcelAdvanced(List<T> dataList, String[] headers, String[] fieldNames,
                                                String sheetName, OutputStream outputStream, 
                                                boolean isXlsx, ExcelExportConfig config) {
        Workbook workbook = null;
        try {
            workbook = isXlsx ? new XSSFWorkbook() : new HSSFWorkbook();
            Sheet sheet = workbook.createSheet(sheetName != null ? sheetName : "Sheet1");
            
            // 设置默认列宽
            setDefaultColumnWidth(sheet, headers.length, config.getDefaultColumnWidth());
            
            // 创建样式
            CellStyle headerStyle = createCustomHeaderStyle(workbook, config);
            CellStyle dataStyle = createCustomDataStyle(workbook, config);
            
            // 创建表头行
            Row headerRow = sheet.createRow(0);
            headerRow.setHeight(config.getHeaderRowHeight());
            
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(headerStyle);
            }
            
            // 填充数据
            if (dataList != null && !dataList.isEmpty()) {
                int rowNum = 1;
                for (T data : dataList) {
                    Row row = sheet.createRow(rowNum++);
                    row.setHeight(config.getDataRowHeight());
                    fillRowData(row, data, fieldNames, dataStyle);
                }
            }
            
            // 根据配置决定是否自适应列宽
            if (config.isAutoSizeColumns()) {
                autoSizeColumns(sheet, headers.length, dataList, fieldNames, 
                              config.getMinColumnWidth(), config.getMaxColumnWidth());
            }
            
            workbook.write(outputStream);
            
        } catch (Exception e) {
            throw new RuntimeException("导出Excel失败", e);
        } finally {
            closeWorkbook(workbook);
        }
    }

    // ==================== 导入相关方法 ====================
    
    /**
     * 基础导入
     */
    public static <T> List<T> importFromExcel(InputStream inputStream, String[] headers, 
                                             String[] fieldNames, Class<T> clazz, boolean isXlsx) {
        ExcelImportConfig<T> config = new ExcelImportConfig<>(clazz)
            .expectedHeaders(headers);
        
        // 建立列映射
        for (int i = 0; i < headers.length && i < fieldNames.length; i++) {
            config.columnMapping(headers[i], fieldNames[i]);
        }
        
        return importExcel(inputStream, config, isXlsx);
    }
    
    /**
     * 智能导入Excel（支持列互换）
     */
    public static <T> List<T> importExcel(InputStream inputStream, 
                                         ExcelImportConfig<T> config, 
                                         boolean isXlsx) {
        List<T> resultList = new ArrayList<>();
        Workbook workbook = null;
        int successCount = 0;
        int errorCount = 0;
        
        try {
            workbook = isXlsx ? new XSSFWorkbook(inputStream) : new HSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            
            if (sheet == null) {
                throw new RuntimeException("Excel文件中没有工作表");
            }
            
            // 解析表头并建立列映射
            Map<Integer, String> columnFieldMap = parseHeaderAndCreateMapping(sheet, config);
            
            if (columnFieldMap.isEmpty()) {
                throw new RuntimeException("无法建立列映射关系，请检查表头配置");
            }
            
            // 读取数据行
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null || isEmptyRow(row)) continue;
                
                try {
                    T obj = parseRowToObject(row, columnFieldMap, config);
                    if (obj != null) {
                        resultList.add(obj);
                        successCount++;
                        
                        // 调用导入监听器
                        if (config.getImportListener() != null) {
                            config.getImportListener().onRowImported(obj, i);
                        }
                    }
                } catch (Exception e) {
                    errorCount++;
                    // 行解析错误处理
                    if (config.getImportListener() != null) {
                        config.getImportListener().onRowError(i, e);
                    } else {
                        System.err.println("第 " + (i+1) + " 行解析失败: " + e.getMessage());
                    }
                }
            }
            
            // 导入完成回调
            if (config.getImportListener() != null) {
                config.getImportListener().onComplete(successCount, errorCount);
            }
            
        } catch (Exception e) {
            throw new RuntimeException("导入Excel失败: " + e.getMessage(), e);
        } finally {
            closeWorkbook(workbook);
            closeStream(inputStream);
        }
        
        return resultList;
    }

    // ==================== 私有工具方法 ====================
    
    private static void setDefaultColumnWidth(Sheet sheet, int columnCount) {
        setDefaultColumnWidth(sheet, columnCount, DEFAULT_COLUMN_WIDTH);
    }
    
    private static void setDefaultColumnWidth(Sheet sheet, int columnCount, int width) {
        for (int i = 0; i < columnCount; i++) {
            sheet.setColumnWidth(i, width * 256);
        }
    }
    
    private static <T> void autoSizeColumns(Sheet sheet, int columnCount, List<T> dataList, String[] fieldNames) {
        autoSizeColumns(sheet, columnCount, dataList, fieldNames, MIN_COLUMN_WIDTH, MAX_COLUMN_WIDTH);
    }
    
    private static <T> void autoSizeColumns(Sheet sheet, int columnCount, List<T> dataList, 
                                          String[] fieldNames, int minWidth, int maxWidth) {
        for (int i = 0; i < columnCount; i++) {
            sheet.autoSizeColumn(i);
            
            int currentWidth = sheet.getColumnWidth(i);
            int charWidth = currentWidth / 256;
            
            if (charWidth < minWidth) {
                sheet.setColumnWidth(i, minWidth * 256);
            } else if (charWidth > maxWidth) {
                sheet.setColumnWidth(i, maxWidth * 256);
            }
            
            int adjustedWidth = sheet.getColumnWidth(i);
            sheet.setColumnWidth(i, adjustedWidth + 512);
        }
    }
    
    private static CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        
        style.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        
        style.setBorderBottom(BorderStyle.MEDIUM);
        style.setBorderTop(BorderStyle.MEDIUM);
        style.setBorderLeft(BorderStyle.MEDIUM);
        style.setBorderRight(BorderStyle.MEDIUM);
        
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 14);
        font.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(font);
        
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(true);
        
        return style;
    }
    
    private static CellStyle createDataStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(true);
        
        DataFormat format = workbook.createDataFormat();
        style.setDataFormat(format.getFormat("@"));
        
        return style;
    }
    
    private static CellStyle createCustomHeaderStyle(Workbook workbook, ExcelExportConfig config) {
        CellStyle style = workbook.createCellStyle();
        
        if (config.getHeaderBgColor() != null) {
            style.setFillForegroundColor(config.getHeaderBgColor().getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }
        
        style.setBorderBottom(config.getHeaderBorderStyle());
        style.setBorderTop(config.getHeaderBorderStyle());
        style.setBorderLeft(config.getHeaderBorderStyle());
        style.setBorderRight(config.getHeaderBorderStyle());
        
        Font font = workbook.createFont();
        font.setBold(config.isHeaderBold());
        font.setFontHeightInPoints(config.getHeaderFontSize());
        if (config.getHeaderFontColor() != null) {
            font.setColor(config.getHeaderFontColor().getIndex());
        }
        style.setFont(font);
        
        style.setAlignment(config.getHeaderAlignment());
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(config.isWrapText());
        
        return style;
    }
    
    private static CellStyle createCustomDataStyle(Workbook workbook, ExcelExportConfig config) {
        CellStyle style = workbook.createCellStyle();
        
        style.setBorderBottom(config.getDataBorderStyle());
        style.setBorderTop(config.getDataBorderStyle());
        style.setBorderLeft(config.getDataBorderStyle());
        style.setBorderRight(config.getDataBorderStyle());
        
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        
        if (config.getDataAlignment() != null) {
            style.setAlignment(config.getDataAlignment());
        }
        
        style.setWrapText(config.isWrapText());
        
        return style;
    }
    
    private static <T> void fillRowData(Row row, T data, String[] fieldNames, CellStyle style) {
        try {
            for (int i = 0; i < fieldNames.length; i++) {
                Cell cell = row.createCell(i);
                cell.setCellStyle(style);
                
                String fieldName = fieldNames[i];
                Object value = getFieldValue(data, fieldName);
                setCellValue(cell, value);
            }
        } catch (Exception e) {
            throw new RuntimeException("填充行数据失败", e);
        }
    }
    
    private static Object getFieldValue(Object obj, String fieldName) {
        try {
            Field field = getField(obj.getClass(), fieldName);
            if (field == null) return "";
            
            field.setAccessible(true);
            return field.get(obj);
        } catch (Exception e) {
            return "";
        }
    }
    
    private static void setCellValue(Cell cell, Object value) {
        if (value == null) {
            cell.setCellValue("");
        } else if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Number) {
            if (value instanceof Integer || value instanceof Long || 
                value instanceof Double || value instanceof Float) {
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof BigDecimal) {
                cell.setCellValue(((BigDecimal) value).doubleValue());
            }
        } else if (value instanceof Date) {
            cell.setCellValue((Date) value);
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else {
            cell.setCellValue(value.toString());
        }
    }
    
    private static <T> Map<Integer, String> parseHeaderAndCreateMapping(Sheet sheet, 
                                                                       ExcelImportConfig<T> config) {
        Map<Integer, String> columnFieldMap = new HashMap<>();
        Row headerRow = sheet.getRow(0);
        
        if (headerRow == null) {
            throw new RuntimeException("Excel文件缺少表头行");
        }
        
        List<String> actualHeaders = new ArrayList<>();
        for (int i = 0; i < headerRow.getLastCellNum(); i++) {
            Cell cell = headerRow.getCell(i);
            String headerName = getCellValueAsString(cell);
            actualHeaders.add(headerName);
        }
        
        if (config.getExpectedHeaders() != null && config.getExpectedHeaders().length > 0) {
            validateHeaders(actualHeaders, config);
        }
        
        if (!config.getIndexMapping().isEmpty()) {
            columnFieldMap.putAll(config.getIndexMapping());
        } else if (!config.getColumnMapping().isEmpty()) {
            for (int i = 0; i < actualHeaders.size(); i++) {
                String headerName = actualHeaders.get(i);
                String fieldName = config.getColumnMapping().get(headerName);
                if (fieldName != null) {
                    columnFieldMap.put(i, fieldName);
                }
            }
        } else {
            for (int i = 0; i < actualHeaders.size(); i++) {
                String headerName = actualHeaders.get(i);
                if (headerName != null && !headerName.trim().isEmpty()) {
                    String fieldName = toCamelCase(headerName.trim());
                    columnFieldMap.put(i, fieldName);
                }
            }
        }
        
        return columnFieldMap;
    }
    
    private static <T> void validateHeaders(List<String> actualHeaders, 
                                           ExcelImportConfig<T> config) {
        String[] expectedHeaders = config.getExpectedHeaders();
        
        if (config.isStrictHeaderCheck()) {
            if (actualHeaders.size() != expectedHeaders.length) {
                throw new RuntimeException(
                    String.format("表头数量不匹配，期望: %d，实际: %d", 
                                expectedHeaders.length, actualHeaders.size()));
            }
            
            for (int i = 0; i < expectedHeaders.length; i++) {
                String expected = expectedHeaders[i];
                String actual = i < actualHeaders.size() ? actualHeaders.get(i) : "";
                if (!expected.equals(actual)) {
                    throw new RuntimeException(
                        String.format("第 %d 列表头不匹配，期望: '%s'，实际: '%s'", 
                                    i+1, expected, actual));
                }
            }
        } else {
            Set<String> actualHeaderSet = new HashSet<>(actualHeaders);
            for (String expected : expectedHeaders) {
                if (!actualHeaderSet.contains(expected)) {
                    throw new RuntimeException("缺少必要表头: " + expected);
                }
            }
        }
    }
    
    private static <T> T parseRowToObject(Row row, Map<Integer, String> columnFieldMap, 
                                         ExcelImportConfig<T> config) throws Exception {
        T obj = config.getTargetClass().newInstance();
        boolean hasData = false;
        
        for (Map.Entry<Integer, String> entry : columnFieldMap.entrySet()) {
            int columnIndex = entry.getKey();
            String fieldName = entry.getValue();
            
            Cell cell = row.getCell(columnIndex);
            Object cellValue = getCellValue(cell, config);
            
            if (cellValue != null) {
                setFieldValue(obj, fieldName, cellValue, config);
                hasData = true;
            }
        }
        
        return hasData ? obj : null;
    }
    
    private static Object getCellValue(Cell cell, ExcelImportConfig<?> config) {
        if (cell == null) return null;
        
        Object value;
        switch (cell.getCellType()) {
            case STRING:
                value = cell.getStringCellValue();
                if (config.isTrimCellValue() && value != null) {
                    value = value.toString().trim();
                }
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    value = cell.getDateCellValue();
                } else {
                    value = cell.getNumericCellValue();
                }
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case FORMULA:
                try {
                    value = cell.getNumericCellValue();
                } catch (Exception e) {
                    try {
                        value = cell.getStringCellValue();
                    } catch (Exception e2) {
                        value = cell.getCellFormula();
                    }
                }
                break;
            case BLANK:
                value = null;
                break;
            default:
                value = null;
        }
        
        return value;
    }
    
    private static <T> void setFieldValue(T obj, String fieldName, Object value, 
                                         ExcelImportConfig<T> config) {
        try {
            Field field = getField(obj.getClass(), fieldName);
            if (field == null) return;
            
            field.setAccessible(true);
            Class<?> fieldType = field.getType();
            
            CellValueConverter converter = config.getCustomConverters().get(fieldName);
            if (converter != null) {
                value = converter.convert(value, fieldType);
            } else {
                value = convertValue(value, fieldType, config);
            }
            
            field.set(obj, value);
            
        } catch (Exception e) {
            throw new RuntimeException(
                String.format("设置字段 %s 值失败: %s", fieldName, e.getMessage()), e);
        }
    }
    
    private static Object convertValue(Object value, Class<?> targetType, 
                                      ExcelImportConfig<?> config) {
        if (value == null) return null;
        
        try {
            if (targetType == String.class) {
                return value.toString();
            } else if (targetType == Integer.class || targetType == int.class) {
                if (value instanceof Number) {
                    return ((Number) value).intValue();
                } else {
                    return Integer.parseInt(value.toString().trim());
                }
            } else if (targetType == Long.class || targetType == long.class) {
                if (value instanceof Number) {
                    return ((Number) value).longValue();
                } else {
                    return Long.parseLong(value.toString().trim());
                }
            } else if (targetType == Double.class || targetType == double.class) {
                if (value instanceof Number) {
                    return ((Number) value).doubleValue();
                } else {
                    return Double.parseDouble(value.toString().trim());
                }
            } else if (targetType == BigDecimal.class) {
                return new BigDecimal(value.toString().trim());
            } else if (targetType == Date.class) {
                if (value instanceof Date) {
                    return value;
                } else {
                    SimpleDateFormat sdf = new SimpleDateFormat(config.getDateFormat());
                    return sdf.parse(value.toString());
                }
            } else if (targetType == Boolean.class || targetType == boolean.class) {
                if (value instanceof Boolean) {
                    return value;
                } else {
                    String strValue = value.toString().toLowerCase();
                    return "true".equals(strValue) || "1".equals(strValue) || "是".equals(strValue);
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(
                String.format("值转换失败: 值 '%s' 无法转换为 %s", value, targetType.getSimpleName()), e);
        }
        
        return value;
    }
    
    private static String toCamelCase(String headerName) {
        String[] parts = headerName.split("[\\s_]+");
        StringBuilder result = new StringBuilder(parts[0].toLowerCase());
        
        for (int i = 1; i < parts.length; i++) {
            if (!parts[i].isEmpty()) {
                result.append(Character.toUpperCase(parts[i].charAt(0)))
                      .append(parts[i].substring(1).toLowerCase());
            }
        }
        
        return result.toString();
    }
    
    private static boolean isEmptyRow(Row row) {
        for (int i = 0; i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if (cell != null) {
                Object value = getCellValueAsString(cell);
                if (value != null && !value.toString().trim().isEmpty()) {
                    return false;
                }
            }
        }
        return true;
    }
    
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) return "";
        switch (cell.getCellType()) {
            case STRING: return cell.getStringCellValue();
            case NUMERIC: 
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN: return String.valueOf(cell.getBooleanCellValue());
            case FORMULA: return cell.getCellFormula();
            default: return "";
        }
    }
    
    private static Field getField(Class<?> clazz, String fieldName) {
        for (Class<?> superClass = clazz; superClass != Object.class; superClass = superClass.getSuperclass()) {
            try {
                return superClass.getDeclaredField(fieldName);
            } catch (NoSuchFieldException e) {
                // 继续在父类中查找
            }
        }
        return null;
    }
    
    private static void closeWorkbook(Workbook workbook) {
        if (workbook != null) {
            try {
                workbook.close();
            } catch (IOException e) {
                // 忽略关闭异常
            }
        }
    }
    
    private static void closeStream(Closeable stream) {
        if (stream != null) {
            try {
                stream.close();
            } catch (IOException e) {
                // 忽略关闭异常
            }
        }
    }
    
    /**
     * 合并单元格
     */
    public static void mergeCells(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
    }
}